package ru.niir.goodfil.ui.utils.excel;

import antlr.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.StringUtil;
import ru.niir.goodfil.db.StorageDeliveryStatus;
import ru.niir.goodfil.db.dic.DeliveryType;
import ru.niir.goodfil.ui.StorageDeliveryListBean;
import ru.niir.goodfil.ui.utils.totalcount.TotalCount;

import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class StorageDeliveryUnloading {

    public static void storageDeliveryListToExcel(List<StorageDeliveryStatus> storageDeliveryStatusList) {

        TotalCount totalCount = new TotalCount();
        ArrayList<TotalCount.DeliveryCount> deliveryCounts = (ArrayList<TotalCount.DeliveryCount>) totalCount.getTotalDeliveryInfo(storageDeliveryStatusList);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet("0");

        String fontName = "Times New Roman";
        short headerFontHeight = (short) 14;
        short cellFontHeight = (short) 12;
        HSSFCellStyle headerCellStyle = hssfWorkbook.createCellStyle();
        headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        HSSFFont headerCellFont = hssfWorkbook.createFont();
        headerCellFont.setFontName("Times New Roman");
        headerCellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerCellFont.setFontHeightInPoints((short) 14);
        headerCellStyle.setFont(headerCellFont);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
        sheet.setRepeatingRows(new CellRangeAddress(0, 1, -1, -1));

        int j = 0;
        int i = 0;
        HSSFRow header = sheet.createRow(i);
        Cell headerCell = header.createCell(0);
        headerCell.setCellStyle(headerCellStyle);
        headerCell.setCellValue("Лист развоза на " + storageDeliveryStatusList.get(0).getDateAsString());
        i++;

        HSSFRow tableHeader = sheet.createRow(i);

        Cell number = tableHeader.createCell(j);
        number.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, cellFontHeight));
        number.setCellValue("№п.п.");
        j++;

        Cell companyName = tableHeader.createCell(j);
        companyName.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, cellFontHeight));
        companyName.setCellValue("Наименование компании");
        sheet.autoSizeColumn(j);
        j++;

        Cell state = tableHeader.createCell(j);
        state.setCellStyle(getHeaderCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
        state.setCellValue("Статус клиента");
        sheet.autoSizeColumn(j);
        j++;

        Cell creationDate = tableHeader.createCell(j);
        creationDate.setCellStyle(getHeaderCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
        creationDate.setCellValue("Дата создания");
        j++;

        Cell firm = tableHeader.createCell(j);
        firm.setCellStyle(getHeaderCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
        firm.setCellValue("Фирма отправитель");
        j++;

        Cell orderVolume = tableHeader.createCell(j);
        orderVolume.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, cellFontHeight));
        orderVolume.setCellValue("Объем заказа, м3");
        j++;

        Cell orderWeight = tableHeader.createCell(j);
        orderWeight.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, cellFontHeight));
        orderWeight.setCellValue("Вес заказа, кг");
        j++;

        Cell manager = tableHeader.createCell(j);
        manager.setCellStyle(getHeaderCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
        manager.setCellValue("Менеджер, тел.");
        sheet.autoSizeColumn(j);
        j++;

        Cell sum = tableHeader.createCell(j);
        sum.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, cellFontHeight));
        sum.setCellValue("Сумма");
        sheet.autoSizeColumn(j);
        j++;

        Cell orderType = tableHeader.createCell(j);
        orderType.setCellStyle(getHeaderCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
        orderType.setCellValue("Форма оплаты");
        j++;

        Cell documentNumIS = tableHeader.createCell(j);
        documentNumIS.setCellStyle(getHeaderCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
        documentNumIS.setCellValue("Номер документа ИС");
        j++;

        Cell documentNum1C = tableHeader.createCell(j);
        documentNum1C.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, cellFontHeight));
        documentNum1C.setCellValue("Номер документа 1С");
        j++;

        Cell deliveryInfo = tableHeader.createCell(j);
        deliveryInfo.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, cellFontHeight));
        deliveryInfo.setCellValue("Информация для водителей");
        sheet.autoSizeColumn(j);
        j++;

        Cell isDelivery = tableHeader.createCell(j);
        isDelivery.setCellStyle(getHeaderCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
        isDelivery.setCellValue("Статус заказа");
        j++;

        Cell comment = tableHeader.createCell(j);
        comment.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, cellFontHeight));
        comment.setCellValue("Комментарий склада");
        sheet.autoSizeColumn(j);

        j++;
        Cell driver = tableHeader.createCell(j);
        driver.setCellStyle(getHeaderCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
        driver.setCellValue("Водитель - экспедитор");

        i++;
        for (StorageDeliveryStatus item : storageDeliveryStatusList) {


            j = 0;
            HSSFRow row = sheet.createRow(i);

            Cell _number = row.createCell(j);
            _number.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _number.setCellValue(i - 1);
            j++;

            Cell _companyName = row.createCell(j);
            _companyName.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
            _companyName.setCellValue(item.getOrder().getClient().getCompanyName());
            j++;

            Cell _state = row.createCell(j);
            _state.setCellStyle(getCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
            _state.setCellValue(item.getOrder().getClient().getClientStateAsString());
            j++;

            Cell _creationDate = row.createCell(j);
            _creationDate.setCellStyle(getCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
            _creationDate.setCellValue(item.getOrder().getCreationDateAsString());
            j++;

            Cell _firm = row.createCell(j);
            _firm.setCellStyle(getCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
            _firm.setCellValue(item.getOrder().getFirmAsString());
            j++;

            Cell _orderVolume = row.createCell(j);
            _orderVolume.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _orderVolume.setCellValue(new BigDecimal(item.getOrder().getOrderVolume()).setScale(2, RoundingMode.UP).doubleValue());
            j++;

            Cell _orderWeight = row.createCell(j);
            _orderWeight.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _orderWeight.setCellValue(item.getOrder().getOrderWeight());
            j++;

            Cell _manager = row.createCell(j);
            _manager.setCellStyle(getCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
            _manager.setCellValue(item.getOrder().getManager().getFio() + item.getOrder().getManager().getTelephoneNumber());
            j++;

            Cell _sum = row.createCell(j);
            _sum.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _sum.setCellValue(item.getOrder().getFactSum());
            j++;

            Cell _orderType = row.createCell(j);
            _orderType.setCellStyle(getCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
            _orderType.setCellValue(item.getOrder().getOrderTypeAsString());
            j++;

            Cell _documentNumIS = row.createCell(j);
            _documentNumIS.setCellStyle(getCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
            _documentNumIS.setCellValue(item.getOrder().getC1Num());
            j++;

            Cell _documentNum1C = row.createCell(j);
            _documentNum1C.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _documentNum1C.setCellValue(item.getDocumentNum1C());
            j++;

            Cell _deliveryInfo = row.createCell(j);
            _deliveryInfo.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _deliveryInfo.setCellValue(item.getOrder().getDriverInfo());
            j++;

            Cell _isDelivery = row.createCell(j);
            _isDelivery.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _isDelivery.setCellValue(item.getStorageDeliveryStateAsString());
            j++;

            Cell _comment = row.createCell(j);
            _comment.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _comment.setCellValue(item.getComment());

            j++;
            Cell _driver = row.createCell(j);
            _driver.setCellStyle(getCellStyleWithRotation(hssfWorkbook, fontName, cellFontHeight));
            _driver.setCellValue(item.getDriver() == null ? "-" : item.getDriver().getFio() + " " + item.getDriver().getCarNumber() + " " + item.getDriver().getPhoneNumber());
            i++;
        }
        //sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(10);
        sheet.autoSizeColumn(13);

        HSSFRow totalDeliveryRow = sheet.createRow(++i);
        Cell totalCell = totalDeliveryRow.createCell(3);
        totalCell.setCellStyle(getHeaderCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        totalCell.setCellValue("Итого");

        Cell delivery = totalDeliveryRow.createCell(4);
        delivery.setCellStyle(getHeaderCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        delivery.setCellValue("Доставлено");

        Cell deliveryVolume = totalDeliveryRow.createCell(5);
        deliveryVolume.setCellStyle(getCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        deliveryVolume.setCellValue("Объем = " + deliveryCounts.get(0).getVolume());

        Cell deliveryWeight = totalDeliveryRow.createCell(6);
        deliveryWeight.setCellStyle(getCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        deliveryWeight.setCellValue("Вес = " + deliveryCounts.get(0).getWeight());

        Cell deliverySum = totalDeliveryRow.createCell(9);
        deliverySum.setCellStyle(getCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        deliverySum.setCellValue("Сумма = " + deliveryCounts.get(0).getSum());
        i++;

        HSSFRow totalUnDeliveryRow = sheet.createRow(i);

        Cell unDelivery = totalUnDeliveryRow.createCell(4);
        unDelivery.setCellStyle(getHeaderCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        unDelivery.setCellValue("Не доставлено");

        Cell unDeliveryVolume = totalUnDeliveryRow.createCell(5);
        unDeliveryVolume.setCellStyle(getCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        unDeliveryVolume.setCellValue("Объем = " + deliveryCounts.get(1).getVolume());

        Cell unDeliveryWeight = totalUnDeliveryRow.createCell(6);
        unDeliveryWeight.setCellStyle(getCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        unDeliveryWeight.setCellValue("Вес = " + deliveryCounts.get(1).getWeight());

        Cell unDeliverySum = totalUnDeliveryRow.createCell(9);
        unDeliverySum.setCellStyle(getCellStyleWithoutBorders(hssfWorkbook, fontName, cellFontHeight));
        unDeliverySum.setCellValue("Сумма = " + +deliveryCounts.get(1).getSum());

        HSSFPrintSetup hssfPrintSetup = sheet.getPrintSetup();
        hssfPrintSetup.setScale((short) 65);
        hssfPrintSetup.setLandscape(true);
        hssfPrintSetup.setHeaderMargin(0);
        hssfPrintSetup.setFooterMargin(0);
        sheet.setMargin(Sheet.LeftMargin, 0);
        sheet.setMargin(Sheet.RightMargin, 0);

        try {
            FacesContext facesContext = FacesContext.getCurrentInstance();
            ExternalContext externalContext = facesContext.getExternalContext();
            HttpServletResponse response = (HttpServletResponse) externalContext.getResponse();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=\"razvoz.xls\"");
            // Запись
            hssfWorkbook.write(response.getOutputStream());
            // Закрытие потока
            facesContext.responseComplete();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void shortStorageDeliveryListToExcel(List<StorageDeliveryStatus> list){
        Collections.sort(list, new Comparator<StorageDeliveryStatus>() {
            @Override
            public int compare(StorageDeliveryStatus o1, StorageDeliveryStatus o2) {
                if(o1.getOrder().getClient().getId() == o2.getOrder().getClient().getId()) return 0;
                else if (o1.getOrder().getClient().getId() < o2.getOrder().getClient().getId()) return  -1;
                else return 1;
            }
        });

/*
        for(StorageDeliveryStatus status : list){
            System.out.println(status.getOrder().getClient().getCompanyName() + "\\" + status.getOrder().getId() + "\\" + status.getDocumentNum1C());
        }
*/

        List<StorageDeliveryStatus> result = new ArrayList<StorageDeliveryStatus>();
        StringBuilder sb = new StringBuilder();
        double orderVolume = 0;
        float generalSum = 0;
        for(int k = 0; k < list.size(); k++){
            if(k != 0 && (list.get(k).getOrder().getClient().getId() == list.get(k - 1).getOrder().getClient().getId())){
                if(!org.apache.commons.lang.StringUtils.isBlank(list.get(k).getDocumentNum1C())){
                    sb.append(", " + list.get(k).getDocumentNum1C());
                }
                orderVolume += list.get(k).getOrder().getOrderVolume();
                generalSum += list.get(k).getOrder().getFactSum();
                continue;
            }
            if(!org.apache.commons.lang.StringUtils.isBlank(sb.toString())){
                result.get(result.size() - 1).setDocumentNum1C(result.get(result.size() - 1).getDocumentNum1C() + sb.toString());

                sb.setLength(0);
            }

            if(orderVolume != 0){
                result.get(result.size() - 1).getOrder().setOrderVolume(result.get(result.size() - 1).getOrder().getOrderVolume() + orderVolume);
                orderVolume = 0;
            }

            if(generalSum != 0){
                result.get(result.size() - 1).getOrder().setFactSum(result.get(result.size() - 1).getOrder().getFactSum() + generalSum);
                generalSum = 0;
            }
            result.add(list.get(k));
        }

        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet("0");
        String fontName = "Times New Roman";
        short headerFontHeight = (short) 12;
        short cellFontHeight = (short) 10;
        HSSFCellStyle headerCellStyle = hssfWorkbook.createCellStyle();
        headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        HSSFFont headerCellFont = hssfWorkbook.createFont();
        headerCellFont.setFontName("Times New Roman");
        headerCellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerCellFont.setFontHeightInPoints((short) 14);
        headerCellStyle.setFont(headerCellFont);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
        sheet.setRepeatingRows(new CellRangeAddress(0, 1, -1, -1));

        int j = 0;
        int i = 0;
        HSSFRow header = sheet.createRow(i);
        Cell headerCell = header.createCell(0);
        headerCell.setCellStyle(headerCellStyle);
        headerCell.setCellValue("Лист развоза на " + list.get(0).getDateAsString());
        i++;
        HSSFRow tableHeader = sheet.createRow(i);
        Cell number = tableHeader.createCell(j);
        number.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        number.setCellValue("№п.п.");
        j++;

        Cell companyName = tableHeader.createCell(j);
        companyName.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        companyName.setCellValue("Наименование компании");
        sheet.autoSizeColumn(j);
        j++;

        Cell firm = tableHeader.createCell(j);
        firm.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        firm.setCellValue("Фирма");
        j++;

        Cell adress = tableHeader.createCell(j);
        adress.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        adress.setCellValue("Адресc доставки");
        sheet.autoSizeColumn(j);
        j++;

        Cell volume = tableHeader.createCell(j);
        volume.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        volume.setCellValue("Объем заказа, м3");
        j++;

        Cell manager = tableHeader.createCell(j);
        manager.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        manager.setCellValue("Менеджер");
        sheet.autoSizeColumn(j);
        j++;

        Cell payType = tableHeader.createCell(j);
        payType.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        payType.setCellValue("Оплата");
        sheet.autoSizeColumn(j);
        j++;

        Cell c1num = tableHeader.createCell(j);
        c1num.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        c1num.setCellValue("№ докум.");
        sheet.autoSizeColumn(j);
        j++;

        Cell sum = tableHeader.createCell(j);
        sum.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        sum.setCellValue("Сумма");
        j++;

        Cell driverInfo = tableHeader.createCell(j);
        driverInfo.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
        driverInfo.setCellValue("Информация для водителей");
        sheet.autoSizeColumn(j);
        i++;

        for(StorageDeliveryStatus status : result){
            j = 0;
            HSSFRow row = sheet.createRow(i);

            Cell _number = row.createCell(j);
            _number.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _number.setCellValue(i - 1);
            sheet.autoSizeColumn(j);
            j++;

            Cell _companyName = row.createCell(j);
            _companyName.setCellStyle(getHeaderCellStyle(hssfWorkbook, fontName, headerFontHeight));
            _companyName.setCellValue(status.getOrder().getClient().getCompanyName());
            j++;

            Cell _firm = row.createCell(j);
            _firm.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _firm.setCellValue(status.getOrder().getFirmAsString());
            sheet.autoSizeColumn(j);
            j++;

            Cell _adress = row.createCell(j);
            _adress.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _adress.setCellValue(status.getOrder().getAdress());
            j++;

            Cell _volume = row.createCell(j);
            _volume.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _volume.setCellValue(new BigDecimal(status.getOrder().getOrderVolume()).setScale(2, RoundingMode.UP).doubleValue());
            j++;

            Cell _manager = row.createCell(j);
            _manager.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _manager.setCellValue(status.getOrder().getManager().getFio() + status.getOrder().getManager().getTelephoneNumber());
            j++;

            Cell _payType = row.createCell(j);
            _payType.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _payType.setCellValue(status.getOrder().getDeliveryInfoExt().getPayTypeAsString());
            j++;

            Cell _c1num = row.createCell(j);
            _c1num.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _c1num.setCellValue(status.getDocumentNum1C());
            j++;

            Cell _sum = row.createCell(j);
            _sum.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _sum.setCellValue(status.getOrder().getFactSum());
            j++;

            Cell _driverInfo = row.createCell(j);
            _driverInfo.setCellStyle(getCellStyle(hssfWorkbook, fontName, cellFontHeight));
            _driverInfo.setCellValue(status.getOrder().getDeliveryComment());
            i++;
        }

        HSSFPrintSetup hssfPrintSetup = sheet.getPrintSetup();
        hssfPrintSetup.setScale((short) 100);
        hssfPrintSetup.setLandscape(true);
        hssfPrintSetup.setHeaderMargin(0);
        hssfPrintSetup.setFooterMargin(0);
        sheet.setMargin(Sheet.LeftMargin, 0);
        sheet.setMargin(Sheet.RightMargin, 0);

        try {
            FacesContext facesContext = FacesContext.getCurrentInstance();
            ExternalContext externalContext = facesContext.getExternalContext();
            HttpServletResponse response = (HttpServletResponse) externalContext.getResponse();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=\"razvoz.xls\"");
            // Запись
            hssfWorkbook.write(response.getOutputStream());
            // Закрытие потока
            facesContext.responseComplete();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static HSSFCellStyle getHeaderCellStyle(HSSFWorkbook hssfWorkbook, String fontName, short fontSize) {

        // Стиль для заголовков по горизонтали
        HSSFCellStyle headerStyle = hssfWorkbook.createCellStyle();

        //Шрифт для заголовков
        HSSFFont headerFont = hssfWorkbook.createFont();
        headerFont.setFontName(fontName);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints(fontSize);

        //Выравнивание
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        headerStyle.setFont(headerFont);
        headerStyle.setWrapText(true);
        // Границы
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return headerStyle;
    }

    private static HSSFCellStyle getHeaderCellStyleWithRotation(HSSFWorkbook hssfWorkbook, String fontName, short fontSize) {

        // Стиль для заголовков по горизонтали
        HSSFCellStyle headerStyle = hssfWorkbook.createCellStyle();

        //Шрифт для заголовков
        HSSFFont headerFont = hssfWorkbook.createFont();
        headerFont.setFontName(fontName);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints(fontSize);

        //Выравнивание
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        headerStyle.setFont(headerFont);
        headerStyle.setWrapText(true);
        // Границы
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setRotation((short) 90);
        return headerStyle;
    }

    private static HSSFCellStyle getCellStyle(HSSFWorkbook hssfWorkbook, String fontName, short fontSize) {

        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //Шрифт для ячеек
        HSSFFont cellFont = hssfWorkbook.createFont();
        cellFont.setFontName(fontName);
        cellFont.setFontHeightInPoints(fontSize);
        cellStyle.setFont(cellFont);
        // Границы
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return cellStyle;
    }

    private static HSSFCellStyle getCellStyleWithRotation(HSSFWorkbook hssfWorkbook, String fontName, short fontSize) {

        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setWrapText(true);

        //Шрифт для ячеек
        HSSFFont cellFont = hssfWorkbook.createFont();
        cellFont.setFontName(fontName);
        cellFont.setFontHeightInPoints(fontSize);
        cellStyle.setFont(cellFont);
        // Границы
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setRotation((short) 90);
        return cellStyle;
    }

    private static HSSFCellStyle getCellStyleWithoutBorders(HSSFWorkbook hssfWorkbook, String fontName, short fontSize) {

        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setWrapText(true);

        //Шрифт для ячеек
        HSSFFont cellFont = hssfWorkbook.createFont();
        cellFont.setFontName(fontName);
        cellFont.setFontHeightInPoints(fontSize);
        cellStyle.setFont(cellFont);
        return cellStyle;
    }

    private static HSSFCellStyle getHeaderCellStyleWithoutBorders(HSSFWorkbook hssfWorkbook, String fontName, short fontSize) {

        // Стиль для заголовков по горизонтали
        HSSFCellStyle headerStyle = hssfWorkbook.createCellStyle();

        //Шрифт для заголовков
        HSSFFont headerFont = hssfWorkbook.createFont();
        headerFont.setFontName(fontName);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints(fontSize);

        //Выравнивание
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        headerStyle.setFont(headerFont);
        headerStyle.setWrapText(true);
        return headerStyle;
    }
}
